SQL JOIN について調べてみた

こんにちは！よしななです。
業務中に SQL のJOINをする機会があったのですが、挙動についてわからない部分があったので備忘録としてまとめます。

目次

• 実行環境
• JOIN について
  • INNER JOIN：内部結合
  • LEFT (OUTER) JOIN：左外部結合
  • RIGHT (OUTER) JOIN：右外部結合
  • FULL JOIN：完全外部結合
  • CROSS JOIN：交差結合

実行環境

本記事では、Amazon Athena で SQL を実行しています。
以下の2つの .jsonl データを Amazon S3 にアップロード済みです。

{"id": "1", "name": "山田太郎", "age": "30"}
{"id": "2", "name": "佐藤花子", "age": "25"}
{"id": "3", "name": "田中一郎", "age": "22"}
{"id": "4", "name": "伊藤美咲", "age": "28"}
{"id": "5", "name": "渡辺健", "age": "35"}
{"id": "6", "name": "小山裕太", "age": "19"}
{"id": "7", "name": "加藤純一", "age": "41"}

{"id": "1", "title": "プログラミング入門", "rental_day": "2023-01-01"}
{"id": "2", "title": "経済学の基礎", "rental_day": "2023-01-02"}
{"id": "3", "title": "世界史概観", "rental_day": "2023-01-03"}
{"id": "4", "title": "美術の理解", "rental_day": "2023-01-04"}
{"id": "5", "title": "現代物理学の展望", "rental_day": "2023-01-05"}
{"id": "1", "title": "人工知能と社会", "rental_day": "2023-01-06"}
{"id": "2", "title": "子どもと教育", "rental_day": "2023-01-07"}
{"id": "3", "title": "心理学入門", "rental_day": "2023-01-08"}
{"id": "4", "title": "日本の名詩選", "rental_day": "2023-01-09"}
{"id": "5", "title": "健康と栄養", "rental_day": "2023-01-10"}
{"id": "1", "title": "地球環境問題", "rental_day": "2023-01-11"}
{"id": "2", "title": "生物学の基本", "rental_day": "2023-01-12"}
{"id": "3", "title": "宇宙の謎", "rental_day": "2023-01-13"}
{"id": "4", "title": "数学の魅力", "rental_day": "2023-01-14"}
{"id": "5", "title": "情報技術の最前線", "rental_day": "2023-01-15"}
{"id": "1", "title": "歴史的建造物の旅", "rental_day": "2023-01-16"}
{"id": "2", "title": "料理の科学", "rental_day": "2023-01-17"}
{"id": "3", "title": "新しい医療の展開", "rental_day": "2023-01-18"}
{"id": "4", "title": "映画理論入門", "rental_day": "2023-01-19"}
{"id": "5", "title": "音楽史", "rental_day": "2023-01-20"}

上記 .jsonl データは ChatGPT を使用して生成しています。

• https://chat.openai.com/

上記 .jsonl データをもとにCREATE TABLEを実行し、Amazon Athena 上にusersテーブル、rental_dayテーブルを作成しています。
※CREATE TABLE手順は こちらの記事 を参考にしてください。

JOIN について

JOINとは、2つ以上のテーブルを結合するSQL構文の一つです。
異なるテーブル間で共通するカラム（キー）を使用し、データを結合します。
JOINには複数種類があるため、次項で SQL 構文と実行結果をまとめます。

INNER JOIN：内部結合

2つのテーブルから、指定した条件に一致する行のみを結合します。
usersテーブル、rental_dayテーブルに存在するidカラムをキーとして、usersテーブル、rental_dayテーブルを結合しています。

SELECT * FROM users
INNER JOIN rental_day
ON users.id = rental_day.id;

実行結果

rental_dayテーブルにid=6,7 のレコードが存在しないため、実行結果にはid=6,7 のレコードは含まれません。

LEFT (OUTER) JOIN：左外部結合

FROM {テーブル名}：左側で指定したテーブルにあるすべての行と、LEFT (OUTER) JOIN {テーブル名}：右側で指定したテーブルにある一致する行をキー：idカラムで結合します。
LEFT (OUTER) JOIN {テーブル名}：右側で指定したテーブルにFROM {テーブル名}：左側で指定したテーブルと一致する行がない場合、結合後のテーブルはNULLになります。
※OUTERはオプショナルです。

SELECT * FROM users
LEFT (OUTER) JOIN rental_day
ON users.id = rental_day.id;

実行結果

usersテーブルにはid=6,7のレコードが存在しますが、rental_dayテーブルにはid=6,7のレコードが存在しないため、実行結果のid=6,7：title,rental_dayカラムはNULLとなります。

RIGHT (OUTER) JOIN：右外部結合

RIGHT (OUTER) JOIN {テーブル名}：右側で指定したテーブルにあるすべての行と、FROM {テーブル名}：左側で指定したテーブルにある一致する行をキー：idカラムで結合します。
FROM {テーブル名}：左側で指定したテーブルに一致する行がない場合、結合後のテーブルはNULLになります。
※OUTERはオプショナルです。

SELECT * FROM users
RIGHT (OUTER) JOIN rental_day
ON users.id = rental_day.id;

実行結果

rental_dayテーブルにはid=6,7のレコードが存在しないため、実行結果にid=6,7のレコードは含まれません。

FULL JOIN：完全外部結合

2つのテーブル間で一致するすべての行をキー：idカラムで結合します。
どちらかのテーブルにしか存在しない行も、結果に含まれます。

SELECT * FROM users
FULL JOIN rental_day
ON users.id = rental_day.id;

実行結果

usersテーブルにはid=6,7のレコードが存在しますが、rental_dayテーブルにはid=6,7のレコードが存在しないため、実行結果のid=6,7：title,rental_dayカラムはNULLとなります。

CROSS JOIN：交差結合

2つのテーブル間で可能な全ての組み合わせが生成されます。

SELECT * FROM "users"
CROSS JOIN "rental_day";

実行結果

id,name × id,title,rental_dayの全ての組み合わせ：合計140通りが実行結果で返ります。
商品のSKU（サイズ × 色）などの組み合わせを生成したい場合に便利ですが、実行結果が大きくなる可能性があるため実行には注意が必要です。

上記の通り、様々な手法でテーブルの結合が可能です。
ここまで読んでいただきありがとうございました！